package com.eorchis.module.trainingquestionnaire.dao.impl;

import java.math.BigInteger;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.UUID;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.ConnectionCallback;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Repository;

import com.eorchis.constants.SystemConstants;
import com.eorchis.core.basedao.dao.impl.HibernateAbstractBaseDao;
import com.eorchis.core.basedao.entity.IBaseEntity;
import com.eorchis.core.basedao.query.condition.IPagingQueryCondition;
import com.eorchis.core.basedao.query.condition.builder.CompareType;
import com.eorchis.core.basedao.query.condition.builder.IConditionBuilder;
import com.eorchis.core.basedao.query.condition.builder.impl.DefaultQueryConditionBuilder;
import com.eorchis.core.page.commond.SortInfoBean;
import com.eorchis.core.ui.commond.IQueryCommond;
import com.eorchis.module.trainingneeds.domain.QuestionBank;
import com.eorchis.module.trainingneeds.querybean.UserInfoBean;
import com.eorchis.module.trainingquestionnaire.dao.IUserAnswerLinkDao;
import com.eorchis.module.trainingquestionnaire.domain.MCQStatisticsBean;
import com.eorchis.module.trainingquestionnaire.domain.QuestionsStatisticsBean;
import com.eorchis.module.trainingquestionnaire.domain.RadioStatisticsBean;
import com.eorchis.module.trainingquestionnaire.domain.ScoreStatisticsBean;
import com.eorchis.module.trainingquestionnaire.domain.StatisticsHeaderBean;
import com.eorchis.module.trainingquestionnaire.domain.UserAnswerLink;
import com.eorchis.module.trainingquestionnaire.domain.UserAnswerLinkQueryBean;
import com.eorchis.module.trainingquestionnaire.domain.json.StudentAnswerSelect;
import com.eorchis.module.trainingquestionnaire.ui.commond.UserAnswerLinkQueryCommond;
import com.eorchis.utils.utils.PropertyUtil;

/**
 * Title: UserAnswerLinkDaoImpl<br>
 * Description: 用户答卷关联信息数据接口实现<br>
 * Company: GoldGov<br>
 * Copyright @ 2012 GoldGov .All rights reserved.<br>
 * @author Max
 * @createDate 2014-08-28
 * @version $Revision$
 */
@SuppressWarnings("unchecked")
@Repository(value = "com.eorchis.module.trainingquestionnaire.dao.impl.UserAnswerLinkDaoImpl")
public class UserAnswerLinkDaoImpl
					extends HibernateAbstractBaseDao
					implements IUserAnswerLinkDao {
	
	public Class<? extends IBaseEntity> entityClass() {
		return UserAnswerLink.class;
	}
	
	@Autowired
	@Qualifier("jdbcTemplate")
	private JdbcTemplate jdbcTemplate;
	
	public void queryConditionProcessor(
			IConditionBuilder defaultQueryConditionBuilder,
			IQueryCommond queryCommond) {
		UserAnswerLinkQueryCommond userAnswerLinkQueryCommond = (UserAnswerLinkQueryCommond)queryCommond;
		defaultQueryConditionBuilder.setBaseQueryString("SELECT t FROM UserAnswerLink t");
		defaultQueryConditionBuilder.addCondition("t.userAnswerId", CompareType.IN, userAnswerLinkQueryCommond.getSearchUserAnswerIds());
		defaultQueryConditionBuilder.addCondition("t.userAnswerId", CompareType.EQUAL,userAnswerLinkQueryCommond.getSearchUserAnswerId());
		defaultQueryConditionBuilder.setQueryStringType(QueryStringType.HQL);
	}
	
	/**
	 * 统计---某一个问卷下所有的主观题
	 * @param queryCommond
	 * @return
	 * @throws Exception
	 * @author Max
	 * @createDate 2014-08-28
	 */
	@Override
	public List<UserAnswerLinkQueryBean> findAllSubjectivesByQnId(UserAnswerLinkQueryCommond qCommond) throws Exception {
		StringBuffer hql=new StringBuffer();
		hql.append("select qnq.questionnaireQuestionId as qqLInkId,qnq.questionBank.questionId as questionId," +
		           " qnq.questionBank.questionName as questionName "+
				   " from QuestionnaireQuestion qnq where 1=1 " );
		
		Map<String, Object> params=new HashMap<String, Object>();
		hql.append(" and qnq.questionnaire.questionnaireId=:questionnaireId and qnq.questionBank.activeState=1 ");
		params.put("questionnaireId", qCommond.getSearchQuestionnaireId());
		if(qCommond.getSearchQuestionType()!=null&&!"".equals(qCommond.getSearchQuestionType())) {
			hql.append(" and qnq.questionBank.questionType.dataCode=:dataCode");
			params.put("dataCode", qCommond.getSearchQuestionType());
		}
		hql.append(" order by qnq.orderNum ");
		
		
		IConditionBuilder defaultQueryConditionBuilder  = new DefaultQueryConditionBuilder();
		defaultQueryConditionBuilder
				.setResultObject(UserAnswerLinkQueryBean.class);
		
		defaultQueryConditionBuilder.setBaseQueryString(hql.toString());
		IPagingQueryCondition queryCondition = (IPagingQueryCondition) defaultQueryConditionBuilder.buliderQueryCondition();
		queryCondition.setParameters(params);
		queryCondition.setQueryStringType(QueryStringType.HQL);
		List<UserAnswerLinkQueryBean> list=(List<UserAnswerLinkQueryBean>) this.findList(queryCondition,
				-1, -1);
		return list;
	}
	
	/**
	 * 统计---根据试题id查询一道主观题的的所有的回答情况
	 * @param queryCommond
	 * @return
	 * @throws Exception
	 * @author Max
	 * @createDate 2014-08-28
	 */
	@Override
	public List<UserAnswerLinkQueryBean> findAllSubjectivesAnByQId(UserAnswerLinkQueryCommond qCommond) throws Exception {
		StringBuffer shql=new StringBuffer();
		shql.append(" select ual.userAnswerId as userAnswerId,q.questionName as questionName,ual.questionAnswer as subjectivesAnswer, " +
		        " d.deptName as deptName,u.userName as userName"+
				" from UserAnswerLink ual right join ual.question q, DepartmentUser du inner join du.user u inner join du.department d");
		
		StringBuffer chql=new StringBuffer();
		chql.append("from UserAnswerLink ual right join ual.question q , DepartmentUser du inner join du.user u inner join du.department d");
		
		
		StringBuffer hql=new StringBuffer();
		hql.append(" where 1=1 " +
				" and ual.user.userId=u.userId");
		
		Map<String, Object> params=new HashMap<String, Object>();
		if(qCommond.getSearchQuestionnaireId()!=null&&!"".equals(qCommond.getSearchQuestionnaireId())) {
			hql.append(" and ual.questionnaire.questionnaireId=:questionnaireId");
			params.put("questionnaireId", qCommond.getSearchQuestionnaireId());
		}
		
		if(qCommond.getSearchQuestionId()!=null&&!"".equals(qCommond.getSearchQuestionId())) {
			hql.append(" and ual.question.questionId=:questionId");
			params.put("questionId", qCommond.getSearchQuestionId());
		}
		
		
		IConditionBuilder defaultQueryConditionBuilder  = new DefaultQueryConditionBuilder();
		defaultQueryConditionBuilder
				.setResultObject(UserAnswerLinkQueryBean.class);
		
		chql.append(hql);
		defaultQueryConditionBuilder.setBaseQueryString(chql.toString());
		IPagingQueryCondition queryCondition = (IPagingQueryCondition) defaultQueryConditionBuilder.buliderQueryCondition();
		queryCondition.setParameters(params);
		//查询总数量
		Long count = this.count(queryCondition);
		int firstResult = qCommond.calculate(count.longValue());
		
		//执行查询
		// 数据列表查询
		shql.append(hql);
		//拼写排序的sql begin
		List<SortInfoBean> sortInfoList = qCommond.getSortInfo();
		if (sortInfoList != null && sortInfoList.size() > 0) {
			SortInfoBean sortInfo = sortInfoList.get(0);
			shql.append(" order by " + PropertyUtil.sqlInjectionFilter(sortInfo.getProperty()) + " " + PropertyUtil.sqlInjectionFilter(sortInfo.getDirection()));
		} else {
			shql.append(" order by u.userId desc");
		}
		defaultQueryConditionBuilder.setBaseQueryString(shql.toString());
		IPagingQueryCondition queryCondition1 = (IPagingQueryCondition) defaultQueryConditionBuilder.buliderQueryCondition();
		queryCondition1.setParameters(params);
		List<UserAnswerLinkQueryBean> list=(List<UserAnswerLinkQueryBean>) this.findList(queryCondition1,
				firstResult, qCommond.getLimit());
		return list;
	}
	
	
	/**
	 * 统计---某一个问卷下所有的客观题
	 * @param queryCommond
	 * @return
	 * @throws Exception
	 * @author Max
	 * @createDate 2014-09-01
	 */
	@Override
	public List<UserAnswerLinkQueryBean> findAllObjectiveByQnId(UserAnswerLinkQueryCommond qCommond) throws Exception {
		StringBuffer sql=new StringBuffer();
		
		sql.append(" SELECT "+
		" b.QUESTION_ID AS itemId, "+
		" b.question_name AS itemTitle, "+
	    " b.question_type as itemType, "+
		" (SELECT count(DISTINCT(qua.userid)) "+
		" FROM "+
		" questionnaire_user_answer qua "+
		" WHERE "+
		" qua.QUESTION_ID = b.QUESTION_ID "+
		" AND q.QUESTIONNAIRE_ID = '"+qCommond.getSearchQuestionnaireId()+"'"+
		" ) AS bigItemTotalNum "+
	    " FROM "+
		" question_bank b "+
	    " INNER JOIN questionnaire_question q ON q.QUESTION_ID = b.QUESTION_ID "+
	    " WHERE "+
		" q.QUESTIONNAIRE_ID = '"+qCommond.getSearchQuestionnaireId()+"'"+
	    " AND b.question_type != '"+QuestionBank.QUESTION_WEN+"' "+
	    " order by q.order_num");

		IConditionBuilder defaultQueryConditionBuilder  = new DefaultQueryConditionBuilder();
		defaultQueryConditionBuilder
				.setResultObject(UserAnswerLinkQueryBean.class);
		defaultQueryConditionBuilder.setBaseQueryString(sql.toString());
		IPagingQueryCondition queryCondition = (IPagingQueryCondition) defaultQueryConditionBuilder.buliderQueryCondition();
		queryCondition.setParameters(null);
		queryCondition.setQueryStringType(QueryStringType.SQL);
		List<UserAnswerLinkQueryBean> list=(List<UserAnswerLinkQueryBean>) this.findList(queryCondition,
				-1, -1);
		
		return list;
	}
	
	/**
	 * 统计---查询客观题中每个题的选项的提交量
	 * @param queryCommond
	 * @return
	 * @throws Exception
	 * @author Max
	 * @createDate 2014-09-01
	 */
	@Override
	public List<UserAnswerLinkQueryBean> findOptionSubmitNum(UserAnswerLinkQueryCommond qCommond) throws Exception{
		StringBuffer sql=new StringBuffer();
		
		sql.append(" SELECT "+
		" bk.QUESTION_ID AS itemId, "+
		" op.option_num AS optionNum, "+
		" op.option_name AS optionName, "+ 
		" bk.QUESTION_TYPE AS itemType,"+
		" ( "+
		"	SELECT "+
		"		count(*) "+
		"	FROM "+
		"		questionnaire_user_answer qua "+
		"	WHERE "+
		"		qua.QUESTION_ID = bk.QUESTION_ID "+
		"	AND qua.QUESTIONNAIRE_ID = qq.QUESTIONNAIRE_ID "+
		"	AND qua.QUESTION_OPTION = op.OPTION_NUM "+
		" ) AS bigSelectNum "+
	    " FROM "+
		" question_bank_option op "+
	    " INNER JOIN question_bank bk ON op.QUESTION_ID = bk.QUESTION_ID "+
	    " INNER JOIN questionnaire_question qq ON qq.QUESTION_ID = bk.QUESTION_ID "+
	    " WHERE "+
		" qq.QUESTIONNAIRE_ID = '"+qCommond.getSearchQuestionnaireId()+"' "+
	    " AND bk.question_type != '"+QuestionBank.QUESTION_WEN+"'"+
	    " ORDER BY "+
		" bk.question_id,"+
		" op.option_num");
		
		
		IConditionBuilder defaultQueryConditionBuilder  = new DefaultQueryConditionBuilder();
		defaultQueryConditionBuilder
				.setResultObject(UserAnswerLinkQueryBean.class);
		defaultQueryConditionBuilder.setBaseQueryString(sql.toString());
		IPagingQueryCondition queryCondition = (IPagingQueryCondition) defaultQueryConditionBuilder.buliderQueryCondition();
		queryCondition.setParameters(null);
		queryCondition.setQueryStringType(QueryStringType.SQL);
		List<UserAnswerLinkQueryBean> list=(List<UserAnswerLinkQueryBean>) this.findList(queryCondition,
				-1, -1);
		return list;
	}
	
	
	/**
	 * 统计---查询试卷提交人员信息
	 * @param queryCommond
	 * @return
	 * @throws Exception
	 * @author Max
	 * @createDate 2014-09-01
	 */
	@Override
	public List<UserInfoBean> getUserListByPnId(UserAnswerLinkQueryCommond qCommond) throws Exception {
		StringBuffer ssql=new StringBuffer();
		ssql.append("SELECT DISTINCT ");
		ssql.append("(a.USERID) AS userId, ");
		ssql.append("b.user_name AS userName, ");
		ssql.append("sex.data_name AS sexName, ");
		ssql.append("b.birthday AS birth, ");
		ssql.append("bd.DEPNAME AS depName, ");
		ssql.append("b.DUTY_REMARK AS dutyRemark, ");
	    ssql.append("duty.DATA_NAME as dutyName, ");
	    ssql.append("b.login_id as loginId, ");
	    ssql.append("a.questionnaire_id  as qnId ");
	    StringBuffer sql=new StringBuffer();
	    sql.append("FROM ");
		sql.append("questionnaire_user_answer a, ");
		sql.append("base_department_user bdu, ");
		sql.append("base_department bd, ");
		sql.append("base_user b ");
	    sql.append("LEFT JOIN base_basedata sex ON sex.data_code = b.sex_code ");
	    sql.append("LEFT JOIN base_basedata duty on duty.DATA_CODE=b.DUTY_LEVEL_CODE ");
	    sql.append("WHERE ");
		sql.append("b.USERID = a.USERID ");
	    sql.append("AND bd.DEPID = bdu.DEPID ");
	    sql.append("AND b.USERID = bdu.USERID ");
	    sql.append("AND a.QUESTIONNAIRE_ID =:qnid ");
		
	    StringBuffer csql=new StringBuffer(" select count(*) ");
	    
	    Map<String, Object> params=new HashMap<String, Object>();
		params.put("qnid", qCommond.getSearchQuestionnaireId());
		
		if(qCommond.getSearchUserName()!=null&&!"".equals(qCommond.getSearchUserName())) {
			sql.append(" and b.user_name like:username");
			params.put("username", "%"+qCommond.getSearchUserName().trim()+"%");
		}
		if(qCommond.getSearchDeptName()!=null&&!"".equals(qCommond.getSearchDeptName())) {
			sql.append(" and bd.DEPNAME like:depname");
			params.put("depname", "%"+qCommond.getSearchDeptName().trim()+"%");
		}
		if(qCommond.getSearchDutyName()!=null&&!"".equals(qCommond.getSearchDutyName())) {
			sql.append(" and b.DUTY_LEVEL_CODE=:dutycode");
			params.put("dutycode", qCommond.getSearchDutyName());
		}
		
		IConditionBuilder defaultQueryConditionBuilder  = new DefaultQueryConditionBuilder();
		defaultQueryConditionBuilder
				.setResultObject(UserInfoBean.class);
		
		csql.append(sql);
		//查询总数量
		List<BigInteger> executeFind=(List<BigInteger>) this.executeFind(QueryStringType.SQL, csql.toString(), params);
		Long count=Long.valueOf(executeFind.get(0).toString());
		//查询总数量
		//计算分页
		int firstResult = qCommond.calculate(count.longValue());
		//计算分页
		//执行查询
		// 数据列表查询
		ssql.append(sql);
		//拼写排序的sql begin
		List<SortInfoBean> sortInfoList = qCommond.getSortInfo();
		if (sortInfoList != null && sortInfoList.size() > 0) {
			SortInfoBean sortInfo = sortInfoList.get(0);
			ssql.append(" order by " + PropertyUtil.sqlInjectionFilter(sortInfo.getProperty()) + " " + PropertyUtil.sqlInjectionFilter(sortInfo.getDirection()));
		} else {
			ssql.append(" order by b.USERID ");
		}
		
		defaultQueryConditionBuilder.setBaseQueryString(ssql.toString());
		IPagingQueryCondition queryCondition1 = (IPagingQueryCondition) defaultQueryConditionBuilder.buliderQueryCondition();
		queryCondition1.setParameters(params);
		queryCondition1.setQueryStringType(QueryStringType.SQL);
		List<UserInfoBean> list=(List<UserInfoBean>) this.findList(queryCondition1,
				firstResult, qCommond.getLimit());
		return list;
	}
	
	
	/**
	 * 统计---查询某个试卷中的所有试题及所选答案
	 * @param queryCommond
	 * @return
	 * @throws Exception
	 * @author Max
	 * @createDate 2014-09-04
	 */
	@Override
	public List<UserAnswerLinkQueryBean> findAllQuestionByQnId(UserAnswerLinkQueryCommond qCommond) throws Exception{
		//StringBuffer sql=new StringBuffer();
		
//		sql.append("SELECT ");
//		sql.append("b.QUESTION_ID AS itemId, ");
//		sql.append("b.question_name AS itemTitle, ");
//		sql.append("b.question_type AS itemType, ");
//		sql.append("op.OPTION_NUM AS optionNum, ");
//		sql.append("op.OPTION_NAME AS optionName ");
//	    sql.append("FROM ");
//		sql.append("question_bank b ");
//	    sql.append("INNER JOIN questionnaire_question q ON q.QUESTION_ID = b.QUESTION_ID ");
//	    sql.append("LEFT JOIN question_bank_option op ON op.QUESTION_ID = b.QUESTION_ID ");
//	    sql.append("WHERE ");
//		sql.append("q.QUESTIONNAIRE_ID = '"+qCommond.getSearchQuestionnaireId()+"' ");
//	    sql.append("ORDER BY q.order_num,op.OPTION_NUM");
		
		String sql=this.getAnswerSql(qCommond);
		
		IConditionBuilder defaultQueryConditionBuilder  = new DefaultQueryConditionBuilder();
		defaultQueryConditionBuilder
				.setResultObject(UserAnswerLinkQueryBean.class);
		defaultQueryConditionBuilder.setBaseQueryString(sql);
		IPagingQueryCondition queryCondition = (IPagingQueryCondition) defaultQueryConditionBuilder.buliderQueryCondition();
		queryCondition.setParameters(null);
		queryCondition.setQueryStringType(QueryStringType.SQL);
		List<UserAnswerLinkQueryBean> list=(List<UserAnswerLinkQueryBean>) this.findList(queryCondition,
				-1, -1);
		return list;
	}
	
	
	private String getAnswerSql(UserAnswerLinkQueryCommond qCommond) {
		StringBuffer sql=new StringBuffer();
		sql.append("SELECT ");
		sql.append("b.QUESTION_ID AS itemId, ");
		sql.append("b.question_name AS itemTitle, ");
		sql.append("b.question_type AS itemType, ");
		sql.append("op.OPTION_NUM AS optionNum, ");
		sql.append("op.OPTION_NAME AS optionName, ");
		sql.append("(");
		sql.append("SELECT ");
		sql.append("qua.QUESTION_OPTION ");
		sql.append("FROM ");
		sql.append("questionnaire_user_answer qua ");
		sql.append("WHERE ");
		sql.append("qua.QUESTIONNAIRE_ID = q.QUESTIONNAIRE_ID ");
		sql.append("AND qua.QUESTION_ID = b.QUESTION_ID ");
		sql.append("AND qua.QUESTION_OPTION = op.OPTION_NUM ");
		sql.append("AND qua.USERID = '"+qCommond.getSearchUserId()+"' ");
		sql.append(") as itemAnswerNum , ");
		sql.append("( ");
		sql.append("SELECT ");
		sql.append("quaa.QUESTION_ANSWER ");
		sql.append("FROM ");
		sql.append("questionnaire_user_answer quaa ");
		sql.append("WHERE ");
		sql.append("quaa.QUESTIONNAIRE_ID = q.QUESTIONNAIRE_ID ");
		sql.append("AND quaa.QUESTION_ID = b.QUESTION_ID ");
		sql.append("AND b.QUESTION_TYPE = 'wendt' ");
		sql.append("AND quaa.USERID = '"+qCommond.getSearchUserId()+"' ");
		sql.append(") as itemAnswerStr ");
		sql.append("FROM ");
		sql.append("question_bank b ");
		sql.append("INNER JOIN questionnaire_question q ON q.QUESTION_ID = b.QUESTION_ID ");
		sql.append("LEFT JOIN question_bank_option op ON op.QUESTION_ID = b.QUESTION_ID ");
		sql.append("WHERE ");
		sql.append("q.QUESTIONNAIRE_ID = '"+qCommond.getSearchQuestionnaireId()+"'");
//		sql.append("ORDER BY b.question_type,q.order_num,op.OPTION_NUM ");
		return sql.toString();
	}
	
	
	/**
	 * 用户提交试卷，答案信息批量入库
	 * @param queryCommond
	 * @return
	 * @throws Exception
	 * @author Max
	 * @createDate 2014-09-09
	 */
	@Override
	public boolean addUserAnswerLink(final UserAnswerLinkQueryCommond qCommond) throws Exception{
		return jdbcTemplate.execute(new ConnectionCallback<Boolean>() {
			public Boolean doInConnection(Connection conn) throws SQLException,
					DataAccessException {
				try {
					
					List<StudentAnswerSelect> list=qCommond.getStudentAnswerSelect();
					
					if(list!=null&&list.size()>0) {
						StringBuffer sql = new StringBuffer();
						 sql.append("insert into questionnaire_user_answer ");
						 sql.append(" (USER_ANSWER_ID,USERID,QUESTIONNAIRE_ID,QUESTION_ID,QUESTION_ANSWER,QUESTION_OPTION,CREATE_DATE,ACTIVE_STATE,CLASS_ID)");
						 sql.append(" values (?,?,?,?,?,?,?,?,?)");
						 PreparedStatement pst = conn.prepareStatement(sql.toString());
						 for(int i=0;i<list.size();i++) {
							 StudentAnswerSelect sas=list.get(i);
							 //循环用户的选择项或者主观题的答案
							 for(int j=0;j<sas.getStudentResult().size();j++) {
								 String answer=sas.getStudentResult().get(j);
								 pst.setString(1, UUID.randomUUID().toString().replace("-", ""));
								 pst.setString(2, qCommond.getSearchUserId());//用户id
								 pst.setString(3, qCommond.getSearchQuestionnaireId());//问卷id
								 pst.setString(4, sas.getItemId());//试题id
								 if(sas.getItemType().equals("wendt")) {
									 pst.setString(5, answer);
									 pst.setString(6, null);
								 }else {
									 if(answer!=null && answer.trim().length()>1){
										 pst.setString(5, answer.substring(1,answer.length()).toString());
										 pst.setString(6, answer.substring(0,1).toString());
									 }else{
										 pst.setString(5, null);
										 pst.setString(6, answer);
									 }
									 
								 }
								 pst.setDate(7, new java.sql.Date(new Date().getTime()));
								 pst.setInt(8, 1);
								 pst.setString(9,qCommond.getSearchClassId());
								 pst.addBatch();
								}
						 }
						 
						 pst.executeBatch();
						 pst.close();
						 return true;
					}else {
						return true;
					}
					
				}catch(Exception e) {
					e.printStackTrace();
					return false;
				}
			}
		});
	}
	/**
	 * 暂存试卷答案信息
	 * @param queryCommond
	 * @return
	 * @throws Exception
	 * @author HuangTF
	 * @createDate 2014-11-05
	 */
	@Override
	public boolean addUserAnswerLinkTemp(final UserAnswerLinkQueryCommond qCommond) throws Exception
	{
		return jdbcTemplate.execute(new ConnectionCallback<Boolean>() {
			public Boolean doInConnection(Connection conn) throws SQLException,
			DataAccessException {
				try {
					List<StudentAnswerSelect> list=qCommond.getStudentAnswerSelect();
					if(list!=null&&list.size()>0) {
						StringBuffer sql = new StringBuffer();
						sql.append("insert into questionnaire_user_answer_temp ");
						sql.append(" (USER_ANSWER_ID,USERID,QUESTIONNAIRE_ID,QUESTION_ID,QUESTION_ANSWER,QUESTION_OPTION,CREATE_DATE,ACTIVE_STATE)");
						sql.append(" values (?,?,?,?,?,?,?,?)");
						PreparedStatement pst = conn.prepareStatement(sql.toString());
						for(int i=0;i<list.size();i++) {
							StudentAnswerSelect sas=list.get(i);
							//循环用户的选择项或者主观题的答案
							for(int j=0;j<sas.getStudentResult().size();j++) {
								String answer=sas.getStudentResult().get(j);
								if(answer.equals(""))
									continue;
								pst.setString(1, UUID.randomUUID().toString().replace("-", ""));
								pst.setString(2, qCommond.getSearchUserId());//用户id
								pst.setString(3, qCommond.getSearchQuestionnaireId());//问卷id
								pst.setString(4, sas.getItemId());//试题id
								if(sas.getItemType().equals("wendt")) {
									pst.setString(5, answer);
									pst.setString(6, null);
								}else {
									pst.setString(5, null);
									pst.setString(6, answer);
								}
								pst.setDate(7, new java.sql.Date(new Date().getTime()));
								pst.setInt(8, 1);
								pst.addBatch();
							}
						}
						
						pst.executeBatch();
						pst.close();
						return true;
					}else {
						return true;
					}
					
				}catch(Exception e) {
					e.printStackTrace();
					return false;
				}
			}
		});
	}
	/**
	 * 删除暂存的试卷答案信息
	 * @param queryCommond
	 * @return
	 * @throws Exception
	 * @author HuangTF
	 * @createDate 2014-11-05
	 */
	@Override
	public boolean delUserAnswerLinkTemp(UserAnswerLinkQueryCommond queryCommond) throws Exception
	{
		String sql="delete from questionnaire_user_answer_temp" +
				" where questionnaire_user_answer_temp.QUESTIONNAIRE_ID = :questionnaireId" +
				" and questionnaire_user_answer_temp.USERID = :userId";
		Map<String, Object> params=new HashMap<String, Object>();
		params.put("questionnaireId", queryCommond.getSearchQuestionnaireId());
		params.put("userId", queryCommond.getSearchUserId());
		
		this.executeUpdate(QueryStringType.SQL, sql, params);
		return true;
	}
	/**
	 * 查询暂存的试卷答案信息
	 * @param queryCommond
	 * @return
	 * @throws Exception
	 * @author HuangTF
	 * @createDate 2014-11-05
	 */
	@Override
	public Map<String, String> queryUserAnswerLinkTemp(UserAnswerLinkQueryCommond queryCommond) throws Exception
	{
		String sql="select t.QUESTION_ID,t.QUESTION_ANSWER,t.QUESTION_OPTION" +
				" from questionnaire_user_answer_temp t" +
				" where t.QUESTIONNAIRE_ID = :questionnaireId and t.USERID = :userId";
		Map<String, Object> params=new HashMap<String, Object>();
		params.put("questionnaireId", queryCommond.getSearchQuestionnaireId());
		params.put("userId", queryCommond.getSearchUserId());
		
		List<Object[]> list=(List<Object[]>) this.executeFind(QueryStringType.SQL, sql, params);
		
		Map<String, String> questionAnswerMap=new HashMap<String, String>();
		for(Object[] obj: list)
		{
			String questionId=obj[0].toString();
			String questionAnswer="";
			if(PropertyUtil.objectNotEmpty(obj[1]))
			{
				questionAnswer=obj[1].toString();
			}
			else
				if(PropertyUtil.objectNotEmpty(obj[2]))
				{
					questionAnswer=obj[2].toString();
				}
			
			if(!questionAnswerMap.containsKey(questionId))
			{
				questionAnswerMap.put(questionId, "");
			}
			questionAnswerMap.put(questionId, questionAnswerMap.get(questionId)+questionAnswer);
		}
		
		return questionAnswerMap;
	}
	/**
	 * 查询用户答卷信息
	 * @param queryCommond
	 * @return
	 * @throws Exception
	 * @author HuangTF
	 * @createDate 2014-11-05
	 */
	@Override
	public Map<String, List<String>> queryUserAnswer(UserAnswerLinkQueryCommond queryCommond) throws Exception
	{
		String sql="select t.QUESTION_ID,t.QUESTION_ANSWER,t.QUESTION_OPTION" +
				" from questionnaire_user_answer t" +
				" where t.QUESTIONNAIRE_ID = :questionnaireId and t.USERID = :userId and t.class_id=:classId ";
		Map<String, Object> params=new HashMap<String, Object>();
		params.put("questionnaireId", queryCommond.getSearchQuestionnaireId());
		params.put("userId", queryCommond.getSearchUserId());
		params.put("classId", queryCommond.getSearchClassId());
		List<Object[]> list=(List<Object[]>) this.executeFind(QueryStringType.SQL, sql, params);
		
		Map<String, List<String>> questionAnswerMap=new HashMap<String, List<String>>();
		for(Object[] obj: list)
		{
			String questionId=obj[0].toString();
			String questionAnswer=(obj[2]==null?"":obj[2].toString())+(obj[1]==null?"":obj[1].toString());
			
			if(!questionAnswerMap.containsKey(questionId))
			{
				questionAnswerMap.put(questionId, new ArrayList<String>());
			}
			questionAnswerMap.get(questionId).add(questionAnswer);
		}
		
		return questionAnswerMap;
	}
	/**
	 * 查询统计表头信息
	 * @param queryCommond
	 * @return
	 * @throws Exception
	 * @author LongJZ
	 * @createDate 2016-3-1
	 */
	@Override
	public StatisticsHeaderBean findHeader(UserAnswerLinkQueryCommond queryCommond) throws Exception {
	    StringBuffer sql=new StringBuffer();
	    sql.append("select c.class_name as \"className\", (select ifnull(count(1),0) from ( SELECT count(1) "
	    		+ "FROM QUESTIONNAIRE_USER_ANSWER qua LEFT JOIN training_class_user tcu ON qua.userid = tcu.user_id "
	    		+ "AND tcu.class_id = qua.class_id,  "+SystemConstants.SYSTEM_CODE+"uum_department_user du,  "
	    		+SystemConstants.SYSTEM_CODE+"uum_department bd,  "+SystemConstants.SYSTEM_CODE+"uum_user b WHERE "
	    				+ "tcu.user_id = b.userid AND b.userid = du.userid AND bd.depid = du.depid AND qua.questionnaire_id =:questionnaireId "
	    				+ "AND qua.class_id =:classId AND qua.active_state = 1 GROUP BY qua.userid ) t) as \"answerUserCount\" "
	    				+ "from TRAINING_CLASS c where c.class_id = :classId");
		
	    Map<String, Object> params=new HashMap<String, Object>();
		params.put("classId", queryCommond.getSearchClassId());
		params.put("questionnaireId", queryCommond.getSearchQuestionnaireId());
		List<StatisticsHeaderBean> resultList = (List<StatisticsHeaderBean>) this.executeFind(QueryStringType.SQL, sql.toString(), params, StatisticsHeaderBean.class);
		return PropertyUtil.objectNotEmpty(resultList) ? resultList.get(0) : null;
	}
	/**
	 * 查询数值型单选题统计列表
	 * @param queryCommond
	 * @return
	 * @throws Exception
	 * @author LongJZ
	 * @createDate 2016-2-24
	 */
	@Override
	public List<ScoreStatisticsBean> findScoreRadioList(UserAnswerLinkQueryCommond queryCommond) throws Exception {
	    StringBuffer sql = new StringBuffer();
	    sql.append("select t.class_name 											  as \"className\"," +
	    		"       t.questions_problem_sets 									  as \"testletName\"," +
	    		"       t.question_id 												  as \"questionId\"," +
	    		"       t.question_name 											  as \"questionName\"," +
	    		"       ifnull(sum(t.option_name),0) 									  as \"unitermSum\"," +
	    		"       ifnull(round(avg(t.option_name), 2),0)								  as \"unitermAvg\"," +
	    		"       ifnull(t.sets_sum,0) 					as \"testletSum\"," +
	    		"       ifnull(t.sets_avg,0)					as \"testletAvg\"," +
	    		"       ifnull(t.total_sum,0)					as \"totalSum\"," +
	    		"       ifnull(t.total_avg,0)			as \"totalAvg\"," +
	    		//去掉以下逻辑，将原逻辑的allAvg变成和totalAvg一样   update by NingSS
	    		"       ifnull(t.total_avg,0) 											  as \"allAvg\"," +
	    		
	    		/*"       nvl((select round(avg(q.a), 2)" +
	    		"          from (select avg(zo.option_name) as a" +
	    		"                  from QUESTIONNAIRE_QUESTION    zqq," +
	    		"                       QUESTIONNAIRE_USER_ANSWER zqua," +
	    		"                       QUESTION_BANK             zb," +
	    		"                       Question_Bank_Option      zo" +
	    		"                 where zqua.questionnaire_id = zqq.questionnaire_id" +
	    		"                   and zqua.question_id = zb.question_id" +
	    		"                   and zqq.question_id = zb.question_id" +
	    		"                   and zo.question_id = zb.question_id" +
	    		"                   and zqua.question_option = zo.option_num" +
	    		"                   and zb.question_type = 'danxt'" +
	    		"                   and zb.is_score_type = 1" +
	    		"                   and zb.active_state = 1" +
	    		"                   and zqua.active_state = 1" +
	    		"                   and zqua.class_id = :classId" +
	    		"                   and zqua.questionnaire_id = :questionnaireId" +
	    		"                 group by zb.questions_problem_sets) q),0) as \"allAvg\"," +*/
	    		"       t.set_count as \"testletCount\"," +
	    		"       t.answer_user_count as \"answerUserCount\"" +
	    		"  from (select c.class_name," +
	    		"               b.questions_problem_sets," +
	    		"               b.question_id," +
	    		"               b.question_name," +
	    		"               o.option_name," +
	    		"               qua.userid," +
	    		"               qq.order_num," +
	    		"               (select sum(zo.option_name)" +
	    		"                  from QUESTIONNAIRE_QUESTION    zqq," +
	    		"                       QUESTIONNAIRE_USER_ANSWER zqua," +
	    		"                       QUESTION_BANK             zb," +
	    		"                       Question_Bank_Option      zo" +
	    		"                 where zqua.questionnaire_id = zqq.questionnaire_id" +
	    		"                   and zqua.question_id = zb.question_id" +
	    		"                   and zqq.question_id = zb.question_id" +
	    		"                   and zo.question_id = zb.question_id" +
	    		"                   and zqua.question_option = zo.option_num" +
	    		"                   and zb.is_score_type = 1" +
	    		"                   and zqua.class_id = :classId" +
	    		"                   and zb.questions_problem_sets = b.questions_problem_sets" +
	    		"                 group by zb.questions_problem_sets) as sets_sum," +
	    		"               round((select avg(zo.option_name)" +
	    		"                       from QUESTIONNAIRE_QUESTION    zqq," +
	    		"                            QUESTIONNAIRE_USER_ANSWER zqua," +
	    		"                            QUESTION_BANK             zb," +
	    		"                            Question_Bank_Option      zo" +
	    		"                      where zqua.questionnaire_id = zqq.questionnaire_id" +
	    		"                        and zqua.question_id = zb.question_id" +
	    		"                        and zqq.question_id = zb.question_id" +
	    		"                        and zo.question_id = zb.question_id" +
	    		"                        and zqua.question_option = zo.option_num" +
	    		"                        and zb.is_score_type = 1" +
	    		"                        and zb.active_state = 1" +
	    		"                        and zqua.active_state = 1" +
	    		"                        and zqua.class_id = :classId" +
	    		"                        and zb.questions_problem_sets = b.questions_problem_sets" +
	    		"                      group by zb.questions_problem_sets), 2) as sets_avg," +
	    		"               (select sum(zo.option_name)" +
	    		"                  from QUESTIONNAIRE_USER_ANSWER zqua, QUESTION_BANK zb," +
	    		"                       Question_Bank_Option zo" +
	    		"                 where zqua.question_id = zb.question_id" +
	    		"                   and zo.question_id = zb.question_id" +
	    		"                   and zqua.question_option = zo.option_num" +
	    		"                   and zb.question_type = 'danxt'" +
	    		"                   and zb.is_score_type = 1" +
	    		"                   and zb.active_state = 1" +
	    		"                   and zqua.active_state = 1" +
	    		"                   and zqua.class_id = :classId" +
	    		"                   and zqua.questionnaire_id = :questionnaireId) as total_sum," +
	    		"               round((select avg(zo.option_name)" +
	    		"                       from QUESTIONNAIRE_USER_ANSWER zqua, QUESTION_BANK zb," +
	    		"                       Question_Bank_Option zo" +
	    		"                      where zqua.question_id = zb.question_id" +
	    		"                        and zo.question_id = zb.question_id" +
	    		"                        and zqua.question_option = zo.option_num" +
	    		"                        and zb.question_type = 'danxt'" +
	    		"                        and zb.is_score_type = 1" +
	    		"                        and zb.active_state = 1" +
	    		"                        and zqua.active_state = 1" +
	    		"                        and zqua.class_id = :classId" +
	    		"                        and zqua.questionnaire_id = :questionnaireId), 2) as total_avg," +
	    		"               (select count(1) from (select count(1)" +
	    		"                          from QUESTIONNAIRE_USER_ANSWER qua" +
	    		"                         where qua.questionnaire_id = :questionnaireId" +
	    		"                         and qua.class_id = :classId" +
	    		"                         and qua.active_state = 1" +
	    		"                         group by qua.userid) b) as answer_user_count," +
	    		"               (select distinct count(1)" +
	    		"                  from QUESTIONNAIRE_QUESTION zqq" +
	    		"                  left join QUESTION_BANK zb on zqq.question_id = zb.question_id" +
	    		"                  left join questionnaire_survey_object so on zqq.questionnaire_id = so.questionnaire_id" +
	    		"                 where zb.question_type = 'danxt'" +
	    		"                   and zb.is_score_type = 1" +
	    		"                   and so.survey_object_id = :classId" +
	    		"                   and zb.questions_problem_sets = b.questions_problem_sets" +
	    		"                 group by zb.questions_problem_sets) as set_count" +
	    		"          from QUESTIONNAIRE_SURVEY_OBJECT qc" +
	    		"         inner join TRAINING_CLASS c" +
	    		"            on qc.survey_object_id = c.class_id" +
	    		"         inner join QUESTIONNAIRE_QUESTION qq" +
	    		"            on qc.questionnaire_id = qq.questionnaire_id" +
	    		"          left join QUESTION_BANK b" +
	    		"            on qq.question_id = b.question_id" +
	    		"          left join QUESTIONNAIRE_USER_ANSWER qua" +
	    		"            on qua.questionnaire_id = qq.questionnaire_id" +
	    		"           and qua.question_id = qq.question_id" +
	    		"           and qua.class_id = qc.survey_object_id" +
	    		"           and qua.active_state = 1" +
	    		"          left join QUESTION_BANK_OPTION o" +
	    		"            on qq.question_id = o.question_id" +
	    		"           and qua.question_option = o.option_num "
	    		+ "left join training_class_user tcu on qua.userid = tcu.user_id "
	    		+ "and tcu.class_id = qua.class_id,  "+SystemConstants.SYSTEM_CODE+"uum_department_user du,  "
	    		+SystemConstants.SYSTEM_CODE+"uum_department d , "+SystemConstants.SYSTEM_CODE+"uum_user bu "
	    		+ "where tcu.user_id = bu.userid and bu.userid = du.userid and d.depid = du.depid and b.question_type = 'danxt'" +
	    		"           and b.active_state = 1" +
	    		"           and b.is_score_type = 1" +
	    		"           and qc.survey_object_id = :classId" +
	    		"           and qc.questionnaire_id = :questionnaireId) t" +
	    		" group by t.class_name," +
	    		"          t.questions_problem_sets," +
	    		"          t.question_id," +
	    		"          t.question_name," +
	    		"          t.order_num" +
	    		" order by t.order_num");
		
	    Map<String, Object> params = new HashMap<String, Object>();
		params.put("classId", queryCommond.getSearchClassId());
		params.put("questionnaireId", queryCommond.getSearchQuestionnaireId());
		List<ScoreStatisticsBean> resultList = (List<ScoreStatisticsBean>) this.executeFind(QueryStringType.SQL, sql.toString(), params, ScoreStatisticsBean.class);
		return resultList;
	}
	/**
	 * 查询文字型单选题统计列表
	 * @param queryCommond
	 * @return
	 * @throws Exception
	 * @author LongJZ
	 * @createDate 2016-2-25
	 */
	@Override
	public List<RadioStatisticsBean> findTextRadioList(UserAnswerLinkQueryCommond queryCommond) throws Exception {
	    StringBuffer sql = new StringBuffer();
	    sql.append("select t.option_id as \"optionId\",t.question_id as \"questionId\",ifnull(t.questions_problem_sets, '无题组') as \"testletName\"," +
	    		"t.question_name as \"questionName\",t.option_name as \"optionName\",t.selected_num as \"selectedNum\"," +
	    		" (select count(1) from Question_Bank_Option zo, QUESTION_BANK zb" +
	    		" where zo.question_id = zb.question_id and zo.question_id = t.question_id and zb.active_state = 1) as \"optionCount\"," +
	    		" (select count(1) from Question_Bank_Option zo, QUESTION_BANK zb, QUESTIONNAIRE_QUESTION zq" +
	    		" where zo.question_id = zb.question_id and zq.question_id = zb.question_id" +
	    		" and zq.questionnaire_id = :questionnaireId and zb.question_type = 'danxt' and zb.is_score_type = 2 and zb.active_state = 1" +
	    		" and (case when zb.questions_problem_sets is null then '无题组' else zb.questions_problem_sets end) =" +
	    		" (case when t.questions_problem_sets is null then '无题组' else t.questions_problem_sets end)) as \"testletCount\"" +
	    		" from" +
	    		" (select o.option_id,o.question_id,b.questions_problem_sets,b.question_name,o.option_name,count(a.userid) as selected_num" +
	    		" from QUESTIONNAIRE_SURVEY_OBJECT qc inner join QUESTIONNAIRE_QUESTION qq on qc.questionnaire_id = qq.questionnaire_id" +
	    		" left join QUESTION_BANK b on qq.question_id = b.question_id" +
	    		" left join Question_Bank_Option o on b.question_id = o.question_id" +
	    		" left join QUESTIONNAIRE_QUESTION q on b.question_id = q.question_id" +
	    		" left join QUESTIONNAIRE_USER_ANSWER a on a.question_id = o.question_id" +
	    		" and a.question_option = o.option_num and a.class_id = qc.survey_object_id and a.active_state = 1" +
	    		" where b.question_type = 'danxt' and b.is_score_type = 2 and b.active_state = 1" +
	    		" and q.questionnaire_id = :questionnaireId and qc.survey_object_id = :classId" +
	    		" group by o.option_id, b.question_name, o.option_name, o.option_num, o.question_id, q.order_num, b.questions_problem_sets" +
	    		" order by q.order_num, o.option_num) t");
		
	    Map<String, Object> params = new HashMap<String, Object>();
		params.put("classId", queryCommond.getSearchClassId());
		params.put("questionnaireId", queryCommond.getSearchQuestionnaireId());
		List<RadioStatisticsBean> resultList = (List<RadioStatisticsBean>) this.executeFind(QueryStringType.SQL, sql.toString(), params, RadioStatisticsBean.class);
		return resultList;
	}
	/**
	 * 查询多选题统计列表
	 * @param queryCommond
	 * @return
	 * @throws Exception
	 * @author LongJZ
	 * @createDate 2016-2-25
	 */
	@Override
	public List<MCQStatisticsBean> findMCQList(UserAnswerLinkQueryCommond queryCommond) throws Exception {
	    StringBuffer sql=new StringBuffer();
	    sql.append("select t.option_id as \"optionId\",t.option_num as \"optionNum\",t.question_id as \"questionId\",t.question_name as \"questionName\"," +
	    		"t.option_name as \"optionName\",t.selected_num as \"selectedNum\"," +
	    		"(select count(1) from Question_Bank_Option zo, QUESTION_BANK zb" +
	    		" where zo.question_id = zb.question_id and zo.question_id = t.question_id and zb.active_state = 1) as \"optionCount\"" +
	    		" from (select o.option_id,o.option_num,o.question_id,b.question_name,o.option_name,count(a.userid) as selected_num" +
	    		" from QUESTIONNAIRE_SURVEY_OBJECT qc" +
	    		" left join QUESTIONNAIRE_QUESTION q on qc.questionnaire_id = q.questionnaire_id" +
	    		" left join QUESTION_BANK b on b.question_id = q.question_id" +
	    		" left join Question_Bank_Option o on b.question_id = o.question_id" +
	    		" left join QUESTIONNAIRE_USER_ANSWER a on a.question_id = o.question_id" +
	    		" and a.question_option = o.option_num and qc.survey_object_id = a.class_id and a.active_state = 1" +
	    		" left join training_class_user tcu on a.userid = tcu.user_id and tcu.class_id = a.class_id, "
	    		+SystemConstants.SYSTEM_CODE+"uum_department_user du,  "+SystemConstants.SYSTEM_CODE+"uum_department d , "
	    		+SystemConstants.SYSTEM_CODE+"uum_user bu where tcu.user_id =bu.userid "
	    		+ " and bu.userid = du.userid  and d.depid = du.depid and b.question_type = 'duoxt'" +
	    		" and q.questionnaire_id = :questionnaireId" +
	    		" and qc.survey_object_id = :classId" +
	    		" and b.active_state = 1" +
	    		" group by o.option_id, b.question_name, o.option_name, o.option_num, o.question_id, q.order_num" +
	    		" order by q.order_num, o.option_num) t");
		
	    Map<String, Object> params=new HashMap<String, Object>();
		params.put("classId", queryCommond.getSearchClassId());
		params.put("questionnaireId", queryCommond.getSearchQuestionnaireId());
		List<MCQStatisticsBean> resultList = (List<MCQStatisticsBean>) this.executeFind(QueryStringType.SQL, sql.toString(), params, MCQStatisticsBean.class);
		return resultList;
	}
	/**
	 * 查询问答题统计列表
	 * @param queryCommond
	 * @return
	 * @throws Exception
	 * @author LongJZ
	 * @createDate 2016-2-26
	 */
	@Override
	public List<QuestionsStatisticsBean> findQuestionsList(UserAnswerLinkQueryCommond queryCommond) throws Exception {
	    StringBuffer sql=new StringBuffer();
	    sql.append("select b.question_id as \"questionId\", b.question_name as \"questionName\"," +
	    		" (select count(distinct a.userid) from QUESTIONNAIRE_USER_ANSWER a" +
	    		" left join training_class_user tcu on a.userid = tcu.user_id and tcu.class_id = a.class_id"
	    		+ ",  "+SystemConstants.SYSTEM_CODE+"uum_department_user du,  "+SystemConstants.SYSTEM_CODE
	    		+"uum_department d , "+SystemConstants.SYSTEM_CODE+"uum_user bu "
	    		+ "where tcu.user_id = bu.userid and bu.userid = du.userid "
	    		+ "and d.depid = du.depid and a.question_id = q.question_id and a.active_state = 1 and a.class_id = :classId " +
	    		" and a.userid in (select tcu.user_id from training_class_user tcu, "
	    		+SystemConstants.SYSTEM_CODE+"uum_department_user du,  "+SystemConstants.SYSTEM_CODE
	    		+"uum_department d , "+SystemConstants.SYSTEM_CODE+"uum_user bu where tcu.user_id = bu.userid "
	    		+ "and bu.userid = du.userid and d.depid = du.depid and tcu.class_id =a.class_Id and tcu.auditing_state in (1, 5,6))) as \"answerUserNum\"" +
	    		" from QUESTION_BANK b left join QUESTIONNAIRE_QUESTION q on b.question_id = q.question_id" +
	    		" where b.question_type = 'wendt' and b.active_state = 1 and q.questionnaire_id = :questionnaireId order by q.order_num");
		
	    Map<String, Object> params=new HashMap<String, Object>();
		params.put("classId", queryCommond.getSearchClassId());
		params.put("questionnaireId", queryCommond.getSearchQuestionnaireId());
		List<QuestionsStatisticsBean> resultList = (List<QuestionsStatisticsBean>) this.executeFind(QueryStringType.SQL, sql.toString(), params, QuestionsStatisticsBean.class);
		return resultList;
	}
	/**
	 * 查询全部用户答卷信息
	 * @param queryCommond
	 * @return
	 * @throws Exception
	 * @author NingSS
	 * @createDate 2016-2-24
	 */
	@Override
	public Map<String, Map<String, List<String>>> queryAllUserAnswer(UserAnswerLinkQueryCommond queryCommond) throws Exception
	{	
		String sql="select bu.login_id, t.question_id, nvl(t.question_answer, qo.option_name) from QUESTIONNAIRE_USER_ANSWER t " +
				"left join base_user bu on t.userid = bu.userid, QUESTION_BANK_OPTION qo where t.questionnaire_id = :questionnaireId " +
				"and qo.question_id = t.question_id and qo.option_num = t.question_option " +//union前一部分是查询单选题和多选题的答案
				"union " +
				"select bu.login_id, t.question_id, t.question_answer from QUESTIONNAIRE_USER_ANSWER t left join base_user bu " +
				"on t.userid = bu.userid where t.questionnaire_id = :questionnaireId and t.question_option is null ";//union后一部分是查询问答题的答案
		Map<String, Object> params=new HashMap<String, Object>();
		params.put("questionnaireId", queryCommond.getSearchQuestionnaireId());
		//执行查询
		List<Object[]> list=(List<Object[]>) this.executeFind(QueryStringType.SQL, sql, params);
		//封装结果Map<学员编号, Map<问题id, 答案集合>>
		Map<String, Map<String, List<String>>> allUserQuestionAnswerMap=new HashMap<String,Map<String, List<String>>>();
		Map<String, List<String>> questionAnswerMap=null;
		for(Object[] obj: list)
		{	
			String loginId=obj[0].toString();//获得学员编号
			//如果学员编号不存在需要将其存入一级map集合
			if(!allUserQuestionAnswerMap.containsKey(loginId))
			{	
				questionAnswerMap=new HashMap<String, List<String>>();
				allUserQuestionAnswerMap.put(loginId, questionAnswerMap);
			}
			
			//学员编号存在后，在二级map中存入试题id，如果此题不存在需要将其存入二级map
			String questionId=obj[1].toString();//获得试题id
			String questionAnswer=(obj[2]==null?"":obj[2].toString());//获得答案
			if(!questionAnswerMap.containsKey(questionId))
			{
				questionAnswerMap.put(questionId, new ArrayList<String>());
			}
			questionAnswerMap.get(questionId).add(questionAnswer);

		}
		return allUserQuestionAnswerMap;
	}

	/**
	 * 删除学员试卷答案信息
	 * @param userIds
	 * @return
	 * @throws Exception
	 * @author LongJZ
	 * @createDate 2016-3-28
	 */
	@Override
	public void delUserAnswerLink(String[] userIds) throws Exception {
		String hql="delete from UserAnswerLink ua where ua.user.userId in (:userIds)";
		Map<String, Object> params=new HashMap<String, Object>();
		params.put("userIds", userIds);
		this.executeUpdate(QueryStringType.HQL, hql, params);
	}
}
